package PassengerVehicle

import java.util.Properties

import org.apache.spark.sql.{DataFrame, SparkSession}

object Factors {
  def main(args: Array[String]): Unit = {
    //创建sparkSession
    val spark: SparkSession = SparkSession.builder().appName("Factors").master("local[4]").config("spark.debug.maxToStringFields", "1000").getOrCreate()
    //创建properties对象 设置连接mysql的信息
    val prop: Properties = new Properties()
    prop.setProperty("user", "root")
    prop.setProperty("password", "123456")

    //读取mysql数据
    val mysqlDF: DataFrame = spark.read.jdbc("jdbc:mysql://127.0.0.1:3306/localsql", "zhjg_jsy_ticket_smz_curmonth", prop)
    val vehicleDF: DataFrame = spark.read.jdbc("jdbc:mysql://127.0.0.1:3306/localsql", "zhjg_zy_vehicle", prop)
    mysqlDF.createOrReplaceTempView("zhjg_jsy_ticket_smz_curmonth")
    vehicleDF.createOrReplaceTempView(("zhjg_zy_vehicle"))

    import spark.sql
    //实际载客量：按照线路进行分组
    var sjzkl = sql("SELECT a.XL,first(b.SEAT_COUNT),count(*) as sjzkl from zhjg_jsy_ticket_smz_curmonth a,zhjg_zy_vehicle b where a.FCRQSJ like '%2019-04%' and a.SFLSXFC='N' and a.SCKCCPHM!=' ' and a.SCKCCPHM=b.VEH_NO GROUP BY a.XL")
    sjzkl.show()
    println(sjzkl.collect().length)

  }
}
